Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class ModeloTributario
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)

        If Not (Page.IsPostBack = True) Then

            btnPesquisar.Enabled = False

            FillDDL()

            If (Session("blBack") = True) Then

                If Not (Session("strOperacao") = "I") Then

                    ddlHolding.SelectedValue = Session("strCodigoDaHolding").ToString
                    FillDDLCompanhia()
                    ddlCompanhia.SelectedValue = Session("strCodigoDaCia").ToString

                    CarregaGVW()

                    btnPesquisar.Enabled = True
                End If

            End If

        End If

        btnConsultar.Enabled = False
        btnIncluir.Enabled = True
        btnAlterar.Enabled = False
        btnExcluir.Enabled = False

    End Sub

    Private Sub FillDDL()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaHolding, CodigoDaHolding + ' - ' + NomeDaHolding AS Holding" & _
                     " FROM Holding" & _
                     " ORDER BY CodigoDaHolding;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlHolding.DataTextField = "Holding"
                ddlHolding.DataValueField = "CodigoDaHolding"
                ddlHolding.DataSource = mydatatable
                ddlHolding.DataBind()

                ddlHolding.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Sub FillDDLCompanhia()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaCia, CodigoDaCia + ' - ' + NomeDaCia AS Cia" & _
                     " FROM Companhia" & _
                     " WHERE CodigoDaHolding = '" & ddlHolding.SelectedValue & "'" & _
                     " ORDER BY CodigoDaCia;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlCompanhia.DataTextField = "Cia"
                ddlCompanhia.DataValueField = "CodigoDaCia"
                ddlCompanhia.DataSource = mydatatable
                ddlCompanhia.DataBind()

                ddlCompanhia.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Sub CarregaGVW()

        Dim strWhere As String = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String = "SELECT A.CodigoDaHolding, A.CodigoDaCia, B.SiglaDaUF AS SiglaDaUFOrigem, C.SiglaDaUF AS SiglaDaUFDestino, A.CodNaturezaOperacao, D.DscCategoriaFiscal, E.DscMeioTransporte, F.DscDestinacaoItem, A.CodigoDaOrigemDoItem, A.DataVigencia" & _
                     " FROM ModeloTributarioHeader A, uf B, uf C, categoriafiscal D, meiotransporte E, destinacaoitem F" & _
                     " WHERE A.CodUFOrigem = B.CodigoDaUF AND A.CodUFDestino = C.CodigoDaUF AND A.CodCategoriaFiscal = D.CodCategoriaFiscal AND A.CodMeioTransporte = E.CodMeioTransporte AND A.CodDestinacaoItem = F.CodDestinacaoItem "

            If Not (ddlHolding.Text = "") Then
                strWhere = " AND A.CodigoDaHolding = '" & ddlHolding.SelectedValue & "'"
            End If

            If Not (ddlCompanhia.Text = "") Then
                strWhere = strWhere & " AND A.CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'"
            End If

            If Not (strWhere = "") Then
                theQueryString = theQueryString & _
                         strWhere
            End If

            theQueryString = theQueryString & " ORDER BY A.CodigoDaHolding, A.CodigoDaCia, B.SiglaDaUF, C.SiglaDaUF;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                gvwData.DataSource = mydatatable
                gvwData.DataBind()
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub



    Protected Sub ddlHolding_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlHolding.SelectedIndexChanged

        btnPesquisar.Enabled = False

        FillDDLCompanhia()

    End Sub

    Protected Sub ddlCompanhia_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCompanhia.SelectedIndexChanged

        btnPesquisar.Enabled = True

    End Sub

    Protected Sub btnPesquisar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPesquisar.Click
        CarregaGVW()
    End Sub

    Protected Sub gvwData_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvwData.PageIndexChanging

        Try
            CarregaGVW()

            gvwData.PageIndex = e.NewPageIndex
            gvwData.DataBind()

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Protected Sub gvwData_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvwData.SelectedIndexChanged

        'If Not Page.IsPostBack Then
        btnConsultar.Enabled = True
        'btnAlterar.Enabled = True
        btnExcluir.Enabled = True
        'End If

    End Sub

    Protected Sub btnConsultar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConsultar.Click

        Session.Add("strOperacao", "C") ' Consultar

        Session.Add("strCodigoDaHolding", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))
        Session.Add("strSiglaDaUFOrigem", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text))
        Session.Add("strSiglaDaUFDestino", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text))
        Session.Add("strCodNaturezaOperacao", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text))
        Session.Add("strDscCategoriaFiscal", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(5).Text))
        Session.Add("strDscMeioTransporte", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(6).Text))
        Session.Add("intCodigoDaOrigemDoItem", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(7).Text))
        Session.Add("strDscDestinacaoItem", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(8).Text))
        Session.Add("strDataVigencia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(9).Text))

        Response.Redirect("ModeloTributarioData.aspx")

    End Sub

    Protected Sub btnIncluir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnIncluir.Click

        Session.Add("strOperacao", "I") ' Incluir

        Session.Add("strCodigoDaHolding", "")
        Session.Add("strCodigoDaCia", "")
        Session.Add("strSiglaDaUFOrigem", "")
        Session.Add("strSiglaDaUFDestino", "")
        Session.Add("strCodNaturezaOperacao", "")
        Session.Add("strDscCategoriaFiscal", "")
        Session.Add("strDscMeioTransporte", "")
        Session.Add("intCodigoDaOrigemDoItem", "")
        Session.Add("strDscDestinacaoItem", "")
        Session.Add("strDataVigencia", "")

        Response.Redirect("ModeloTributarioData.aspx")

    End Sub

    Protected Sub btnAlterar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAlterar.Click

        Session.Add("strOperacao", "A") ' Alterar

        Session.Add("strCodigoDaHolding", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))
        Session.Add("strSiglaDaUFOrigem", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text))
        Session.Add("strSiglaDaUFDestino", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text))
        Session.Add("strCodNaturezaOperacao", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text))
        Session.Add("strDscCategoriaFiscal", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(5).Text))
        Session.Add("strDscMeioTransporte", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(6).Text))
        Session.Add("intCodigoDaOrigemDoItem", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(7).Text))
        Session.Add("strDscDestinacaoItem", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(8).Text))
        Session.Add("strDataVigencia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(9).Text))

        Response.Redirect("ModeloTributarioData.aspx")
        
    End Sub

    Protected Sub btnExcluir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcluir.Click

        Session.Add("strOperacao", "E") ' Alterar

        Session.Add("strCodigoDaHolding", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))
        Session.Add("strSiglaDaUFOrigem", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text))
        Session.Add("strSiglaDaUFDestino", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text))
        Session.Add("strCodNaturezaOperacao", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text))
        Session.Add("strDscCategoriaFiscal", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(5).Text))
        Session.Add("strDscMeioTransporte", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(6).Text))
        Session.Add("intCodigoDaOrigemDoItem", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(7).Text))
        Session.Add("strDscDestinacaoItem", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(8).Text))
        Session.Add("strDataVigencia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(9).Text))

        Response.Redirect("ModeloTributarioData.aspx")

    End Sub

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("Default.aspx")

    End Sub

End Class
